package dao.impl;

import dao.BaseDAO;
import entity.Goods;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.Collection;
import java.util.List;

/**
 * Created by asus on 2017/6/19.
 */
@Repository("goodsDAO")
public class GoodsDAOImpl extends BaseDAO implements dao.GoodsDAO {

    private class GoodsMapper implements RowMapper<Goods>{
        @Override
        public Goods mapRow(ResultSet resultSet, int i) throws SQLException {
            Goods goods=new Goods();
            goods.setId(resultSet.getLong("id"));
            goods.setName(resultSet.getString("name"));
            goods.setPrice(resultSet.getInt("price")/100);
            goods.setQuantity(resultSet.getInt("quantity"));
            goods.setType(resultSet.getString("type"));
            return goods;
        }
    }

    /**
     * 获取总记录数
     * @return
     */
    @Override
    public Integer getCount(){
        String sql="select count(*) from goods";
        try{
            return jdbcTemplate.queryForObject(sql,Integer.class);
        }catch (Exception e){
            e.printStackTrace();
            return 0;
        }

    }

    @Override
    public Goods findById(Long id) {
        String sql = "select * from goods where id=?";
        try{
            return jdbcTemplate.queryForObject(sql, new Object[]{id}, new GoodsMapper());
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }

    }

    public Collection<Goods> findByIds(Collection<Long> ids) {
        StringBuilder sqlStr = new StringBuilder("select * from goods where id in (");
        for (Long id : ids) {
            sqlStr.append(id + ",");
        }
        sqlStr.append(")");
        String sql = sqlStr.deleteCharAt(sqlStr.length() - 2).toString();
        System.out.println(sql);
        try {
            return jdbcTemplate.query(sql, new GoodsMapper());
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public Collection<Goods> findPage(int page, int size) {
        if (page < 1) {
            page = 1;
        }
        if (size < 0) {
            size = 20;
        }
        String sql = "select * from goods limit ?,?";
        int skip = (page - 1) * size;
        try {
            return jdbcTemplate.query(sql, new Object[]{skip, size}, new GoodsMapper());
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public List<Goods> findAll() {
        String sql="select * from goods";
        return jdbcTemplate.query(sql,new GoodsMapper());
    }

    @Override
    public void update(Goods goods) throws Exception{
            String sql = "update goods set name=?,type=?,quantity=?,price=? where id=?";
            jdbcTemplate.update(sql, goods.getName(),goods.getType(),goods.getQuantity(),goods.getPrice(),goods.getId());
    }

    @Override
    public void delete(Long id) throws Exception {
        String sql="delete from goods where id=?";
        jdbcTemplate.update(sql,id);
    }

    @Override
    public Long save(final Goods goods) {
        final String sql="insert into goods(name,type,quantity,price) values(?,?,?,?)";
        KeyHolder keyHolder=new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps=connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                //role 被内部类使用，必须声明为final
                ps.setString(1,goods.getName());
                ps.setString(2,goods.getType());
                ps.setInt(3,goods.getQuantity());
                ps.setInt(4,(int)(goods.getPrice()*100));
                return ps;
            }
        },keyHolder);
        //获取id
         return (keyHolder.getKey().longValue());
    }
}
